package com.samebuilding.dao.impl;

import java.sql.SQLException;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;

import com.samebuilding.dao.LeaveMessageDAO;
import com.samebuilding.domain.LeaveMessage;

public class LeaveMessageDAOImpl extends GenericDAOImpl<LeaveMessage, Integer> implements
		LeaveMessageDAO {

	private static final String DELETE_BY_ID_AND_LEAVEUSERNO = "delete from LeaveMessage where id = ? and leaveUserNo = ?";
	private static final String DELETE_BY_ID_AND_RECEIVEUSERNO = "delete from LeaveMessage where id = ? and receiveUserNo = ?";
	
	private static final String GET_LEAVEMESSAGE = "from LeaveMessage where leaveUserNo = ? order by time desc";
	private static final String GET_COUNT_LEAVEMESSAGE = "select count(id) from LeaveMessage where leaveUserNo = ?";
	private static final String GET_RECEIVEMESSAGE = "from LeaveMessage where receiveUserNo = ? order by time desc";
	private static final String GET_COUNT_RECEIVEMESSAGE = "select count(id) from LeaveMessage where receiveUserNo = ?";
	private static final String GET_COUNT_UNREAD_RECEIVEMESSAGE = "select count(id) from LeaveMessage where receiveUserNo = ? and readed = 0";
	
	private static final String UPDATE_UNREAD_TO_READ = "update LeaveMessage set readed = 1 where receiveUserNo = ? and readed = 0";
	
	
	public void deleteByIdAndLeaveUserNo(final Integer id, final Integer leaveUserNo) {
		getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session s) throws HibernateException,
					SQLException {
				Query query = s.createQuery(DELETE_BY_ID_AND_LEAVEUSERNO);
				query.setInteger(0, id);
				query.setInteger(1, leaveUserNo);
				query.executeUpdate();
				return null;
			}
		});

	}

	public void deleteByIdAndReceiveUserNo(final Integer id, final Integer receiveUserNo) {
		getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session s) throws HibernateException,
					SQLException {
				Query query = s.createQuery(DELETE_BY_ID_AND_RECEIVEUSERNO);
				query.setInteger(0, id);
				query.setInteger(1, receiveUserNo);
				query.executeUpdate();
				return null;
			}
		});

	}

	public int getCountLeaveMessage(final Integer leaveUserNo) {
		long count = (Long)this.getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(GET_COUNT_LEAVEMESSAGE);
				query.setInteger(0, leaveUserNo);

				return query.uniqueResult();
			}
		});
		
		return (int)count;
	}

	public int getCountReceiveMessage(final Integer receiveUserNo) {
		// TODO Auto-generated method stubGET_COUNT_RECEIVEMESSAGE
		long count = (Long)this.getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(GET_COUNT_RECEIVEMESSAGE);
				query.setInteger(0, receiveUserNo);

				return query.uniqueResult();
			}
		});
		System.out.println(count);
		return (int)count;
	}

	public int getCountUnreadReceiveMessage(final Integer receiveUserNo) {
		// TODO Auto-generated method stub GET_COUNT_UNREAD_RECEIVEMESSAGE
		long count = (Long)this.getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(GET_COUNT_UNREAD_RECEIVEMESSAGE);
				query.setInteger(0, receiveUserNo);

				return query.uniqueResult();
			}
		});
		
		return (int)count;
	}

	@SuppressWarnings("unchecked")
	public List<LeaveMessage> getLeaveMessage(final Integer leaveUserNo, final int start,
			final int limit) {
		List<LeaveMessage> list = getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(GET_LEAVEMESSAGE);
				query.setInteger(0, leaveUserNo);
				query.setFirstResult(start);
				query.setMaxResults(limit);
				List<LeaveMessage> list = query.list();
				return list;
			}
		});
		return list;
	}

	@SuppressWarnings("unchecked")
	public List<LeaveMessage> getReceiveMessage(final Integer receiveUserNo,
			final int start, final int limit) {
		List<LeaveMessage> list = getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(GET_RECEIVEMESSAGE);
				query.setInteger(0, receiveUserNo);
				query.setFirstResult(start);
				query.setMaxResults(limit);
				List<LeaveMessage> list = query.list();
				return list;
			}
		});
		return list;
	}

	public void updateUnreadToRead(final Integer receiveUserNo) {
		getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session s) throws HibernateException,
					SQLException {
				Query query = s.createQuery(UPDATE_UNREAD_TO_READ);
				query.setInteger(0, receiveUserNo);
				query.executeUpdate();
				return null;
			}
		});

	}


}
